
GO
/****** Object:  StoredProcedure [dbo].[sp_adm_Insert_PurOrderFutureVersions]    Script Date: 03/09/2010 09:50:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_adm_Insert_PurOrderFutureVersions] 
	@versionID int
AS 
BEGIN
SET NOCOUNT ON
DECLARE @purchOrderVersion INT, @purchOrderID INT, @biweekPeriodVer INT, @capacity int, @quantityToDistr int,@cornFutureVersionId int
DECLARE @futurePeridoVerID INT, @futurePeriodID INT, @cornFutureID INT, @appFuturePerVerId INT, @quantity INT
DECLARE @biweekID TINYINT
DECLARE @curPurchaseOrders CURSOR  
DECLARE @curFutureVer CURSOR
--Obtener las ordenes de compra
SET @curPurchaseOrders = CURSOR FOR 
SELECT	purchase_order_version_id, 
		purchase_order_id, 
		pov.biweek_period_version_id,
		( CONVERT( INT, pov.capacity*(select qty from units where unit_id = 'CON') + .99 ) ) - isnull( (SELECT sum(QUANTITY) FROM purchases_orders_futures_versions WHERE purchases_orders_futures_versions.purchase_order_version_id = pov.purchase_order_version_id),0)
FROM    purchases_orders_versions pov
		inner join biweeks_periods_versions bpv
		on pov.biweek_period_version_id = bpv.biweek_period_version_id and
		   version_id = @versionID
		inner join biweeks_periods bp
		on bpv.biweek_period_id = bp.biweek_period_id
		inner join transports t
		on pov.transport_id = t.transport_id
		left join corns_types ctpov
		on pov.corn_type_id = ctpov.corn_type_id
WHERE	( ( CONVERT( INT, pov.capacity*(select qty from units where unit_id = 'CON') + .99 ) ) - isnull( (SELECT sum(QUANTITY) FROM purchases_orders_futures_versions WHERE purchases_orders_futures_versions.purchase_order_version_id = pov.purchase_order_version_id),0) ) > 0
	and is_transit = 0 and
	( ctpov.corn_origin = 'I' or 
	ctpov.corn_origin is null )
order by pov.future_sort,
		 bp.start_date,
		 t.priority desc,
		 pov.code,
		 pov.code_number 
OPEN @curPurchaseOrders
FETCH NEXT FROM @curPurchaseOrders INTO @purchOrderVersion, @purchOrderID, @biweekPeriodVer, @capacity
WHILE @@FETCH_STATUS=0
	BEGIN
		--Obtener el ID del biweek
		SET @biweekID = (SELECT bwp.month	FROM dbo.biweeks_periods bwp INNER JOIN biweeks_periods_versions BWPV ON BWP.biweek_period_id = BWPV.biweek_period_id and BWPV.biweek_period_version_id = @biweekPeriodVer)
		--OBTENER LAS ORDENES FUTURAS
		PRINT N'ORDEN - ' + RTRIM(@purchOrderVersion) + ' BIWEEK ID - ' + RTRIM(@biweekID) +  ' ' +
		RTRIM(@purchOrderID) +' ' + RTRIM(@biweekPeriodVer) +' ' + RTRIM(@capacity)
	
		SET @curFutureVer = CURSOR FOR
		SELECT  CFV.CORN_FUTURE_VERSION_ID, FPV.future_period_version_id, FPV.future_period_id, 
		CFV.corn_future_id, CFV.application_future_period_version_id, CFV.quantity - isnull( (SELECT sum(QUANTITY) FROM purchases_orders_futures_versions WHERE CFV.CORN_FUTURE_VERSION_ID = purchases_orders_futures_versions.corn_future_version_id),0)
		FROM futures_periods_versions FPV
                        inner join futures_periods FP
                        on FPV.future_period_id = FP.future_period_id
                        inner join corn_futures_versions CFV
                        on CFV.application_future_period_version_id = FPV.future_period_version_id
		WHERE FPV.version_id = @versionID AND (@biweekID BETWEEN  FP.start_month AND FP.end_month )
                      and (CFV.quantity - isnull( (SELECT sum(QUANTITY) FROM purchases_orders_futures_versions WHERE CFV.CORN_FUTURE_VERSION_ID = purchases_orders_futures_versions.corn_future_version_id),0)) > 0
		ORDER BY  FP.start_date, unit_price desc
		OPEN @curFutureVer

		IF CURSOR_STATUS('variable','@curFutureVer') <> 0 
			PRINT N'ORDEN - ' + RTRIM(@purchOrderVersion) + ' BIWEEK ID - ' + RTRIM(@biweekID) + '----SI tiene futuros' 
		ELSE
			PRINT N'ORDEN - ' + RTRIM(@purchOrderVersion) + ' BIWEEK ID - ' + RTRIM(@biweekID) + '----NO TIENE FUTUROS' 


		FETCH NEXT FROM @curFutureVer INTO @cornFutureVersionId, @futurePeridoVerID, @futurePeriodID, @cornFutureID, @appFuturePerVerId, @quantity
		WHILE @@FETCH_STATUS=0
			BEGIN
				PRINT N'DETALLE -  Future Period Version ' + RTRIM(@futurePeridoVerID) + ', Future Period ID ' +  RTRIM(@futurePeriodID) + ', Corn Future ID' +   RTRIM(@cornFutureID) + ' , App Future Period Version' + RTRIM(@appFuturePerVerId) +  ', Quantity ' +  RTRIM(@quantity)
                                
                            
     if @quantity >= @capacity 
                                    set @quantityToDistr = @capacity
                                else
                                    set @quantityToDistr = @quantity
                                
                                INSERT INTO purchases_orders_futures_versions (corn_future_version_id,purchase_order_version_id,quantity) VALUES (@cornFutureVersionId,@purchOrderVersion,@quantityToDistr)
                                
                                IF @quantity >= @capacity 
                                    break
                                else
                                    set @capacity = @capacity - @quantity
				FETCH NEXT FROM @curFutureVer INTO @cornFutureVersionId, @futurePeridoVerID, @futurePeriodID, @cornFutureID, @appFuturePerVerId, @quantity
			END
		CLOSE @curFutureVer
		DEALLOCATE @curFutureVer

		FETCH NEXT FROM @curPurchaseOrders INTO @purchOrderVersion, @purchOrderID, @biweekPeriodVer, @capacity
	END
CLOSE @curPurchaseOrders
DEALLOCATE @curPurchaseOrders

END
